﻿using System;
using System.Diagnostics.CodeAnalysis;
using Dapper;
using Iot.HistoryVault;
using Iot.Shared.Path;
using Microsoft.Extensions.Logging;

namespace Iot.TimingDatabase.EnumTypeCache;

/// <summary>
///     枚举类型缓存SQL实现类，继承自EnumTypeCacheBase并实现ISqlIetHistoryVault接口
/// </summary>
/// <param name="logger">日志记录器</param>
public class EnumTypeCacheSqlIet(ILogger<EnumTypeCacheSqlIet> logger) : EnumTypeCacheBase(logger), ISqlIetHistoryVault
{
    // 用于线程同步的锁对象
    private readonly object _lockObj = new();

    /// <summary>
    ///     静态构造函数，用于初始化数据库表和索引
    /// </summary>
    static EnumTypeCacheSqlIet()
    {
        // 创建EnumType表的SQL语句
        const string createEnumTypeTableSql = """
                                              CREATE TABLE IF NOT EXISTS EnumType (
                                                  Id INTEGER PRIMARY KEY AUTOINCREMENT,
                                                  FullName TEXT NOT NULL,
                                                  AssemblyFullName TEXT NOT NULL,
                                                  UNIQUE (FullName, AssemblyFullName)
                                              )
                                              """;
        // 创建PathEnumCache表的SQL语句
        const string createPathEnumCacheTableSql = """
                                                   CREATE TABLE IF NOT EXISTS PathEnumCache (
                                                       Id INTEGER PRIMARY KEY AUTOINCREMENT,
                                                       Path TEXT NOT NULL,
                                                       EnumTypeId INTEGER NOT NULL,
                                                       FOREIGN KEY (EnumTypeId) REFERENCES EnumType(Id) ON DELETE CASCADE
                                                   )
                                                   """;

        // 执行表创建SQL
        using var connection = ISqlIetHistoryVault.Connection;
        connection.Open();
        connection.Execute(createEnumTypeTableSql);
        connection.Execute(createPathEnumCacheTableSql);

        // 创建索引的SQL语句
        const string ixEnumTypeFullNameAssemblyFullName =
            "CREATE INDEX IF NOT EXISTS IX_EnumType_FullName_AssemblyFullName ON EnumType(FullName, AssemblyFullName)";
        const string ixPathEnumCachePath = "CREATE INDEX IF NOT EXISTS IX_PathEnumCache_Path ON PathEnumCache(Path)";
        const string ixPathEnumCacheEnumTypeId =
            "CREATE INDEX IF NOT EXISTS IX_PathEnumCache_EnumTypeId ON PathEnumCache(EnumTypeId)";
        connection.Execute(ixEnumTypeFullNameAssemblyFullName);
        connection.Execute(ixPathEnumCachePath);
        connection.Execute(ixPathEnumCacheEnumTypeId);
        const string ixEnumTypeNameAssembly =
            "CREATE INDEX IF NOT EXISTS IX_EnumType_Name_Assembly ON EnumType(FullName, AssemblyFullName)";
        connection.Execute(ixEnumTypeNameAssembly);
    }

    /// <summary>
    ///     获取枚举类型缓存模型
    /// </summary>
    /// <param name="keyPath">设备路径</param>
    /// <param name="assemblyFullName">程序集全名输出参数</param>
    /// <param name="typeFullName">类型全名输出参数</param>
    /// <returns>是否成功获取缓存模型</returns>
    protected override bool GetEnumTypeCacheModel(DevicePath keyPath, [NotNullWhen(true)] out string? assemblyFullName,
        [NotNullWhen(true)] out string? typeFullName)
    {
        // 查询SQL语句
        const string query = """
                                 SELECT *
                                 FROM PathEnumCache pec
                                 JOIN EnumType et ON pec.EnumTypeId = et.Id
                                 WHERE pec.Path = @Path
                             """;
        var path = keyPath.ToString();
        EnumType? result;
        // 使用锁确保线程安全
        lock (_lockObj)
        {
            try
            {
                using var connection = ISqlIetHistoryVault.Connection;
                connection.Open();
                result = connection.QueryFirstOrDefault<EnumType>(query, new { Path = path });
            }
            catch (Exception e)
            {
                logger.LogError(e, "获取枚举类型缓存模型时出错，路径为： {Path}", path);
                throw;
            }
        }

        if (result == null)
        {
            assemblyFullName = null;
            typeFullName = null;
            return false;
        }

        assemblyFullName = result.AssemblyFullName;
        typeFullName = result.FullName;
        return true;
    }

    /// <summary>
    ///     设置枚举类型缓存模型
    /// </summary>
    /// <param name="keyPath">设备路径</param>
    /// <param name="assemblyFullName">程序集全名</param>
    /// <param name="typeFullName">类型全名</param>
    /// <returns>是否成功设置缓存模型</returns>
    protected override bool SetEnumTypeCacheModel(DevicePath keyPath, string assemblyFullName, string typeFullName)
    {
        // 根据ID查询的SQL
        const string queryById = """
                                 SELECT Id 
                                 FROM EnumType 
                                 WHERE FullName = @FullName AND AssemblyFullName = @AssemblyFullName
                                 """;
        // 插入EnumType记录的SQL
        const string sql = """

                                       INSERT INTO EnumType (FullName, AssemblyFullName)
                                       VALUES (@FullName, @AssemblyFullName)
                                       RETURNING Id
                           """;
        // 检查PathEnumCache记录是否存在的SQL
        const string checkSql = "SELECT * FROM PathEnumCache WHERE Path = @Path";

        // 插入PathEnumCache记录的SQL
        const string insertSql = """
                                                 INSERT INTO PathEnumCache (Path, EnumTypeId)
                                                 VALUES (@Path, @EnumTypeId)
                                                 RETURNING Id
                                 """;
        // 更新PathEnumCache记录的SQL
        const string updateSql = "UPDATE PathEnumCache SET EnumTypeId = @EnumTypeId WHERE Id = @Id";
        var path = keyPath.ToString();
        PathEnumCache? existingRecord;
        var newId = 0;
        int enumTypeId;
        // 使用锁确保线程安全
        lock (_lockObj)
        {
            using var connection = ISqlIetHistoryVault.Connection;
            connection.Open();
            using var transaction = connection.BeginTransaction();
            try
            {
                // 查询或创建EnumType记录
                enumTypeId =
                    connection.QueryFirstOrDefault<int?>(queryById,
                        new { FullName = typeFullName, AssemblyFullName = assemblyFullName }) ??
                    connection.ExecuteScalar<int>(sql,
                        new EnumType { Id = 0, AssemblyFullName = assemblyFullName, FullName = typeFullName });
                // 检查PathEnumCache记录是否存在
                existingRecord = connection.QueryFirstOrDefault<PathEnumCache>(checkSql, new { Path = path });

                if (existingRecord is null)
                {
                    // 插入新的PathEnumCache记录
                    newId = connection.ExecuteScalar<int>(insertSql,
                        new PathEnumCache { Id = 0, Path = path, EnumTypeId = enumTypeId });
                }
                else if (existingRecord.EnumTypeId != enumTypeId)
                {
                    // 更新现有的PathEnumCache记录
                    newId = connection.Execute(updateSql, new
                    {
                        EnumTypeId = enumTypeId,
                        existingRecord.Id
                    });
                }

                transaction.Commit();
            }
            catch (Exception e)
            {
                transaction.Rollback();
                logger.LogError(e, "设置枚举类型缓存模型时出错，路径为： {Path}", path);
                throw;
            }
        }

        return newId > 0 || existingRecord?.EnumTypeId == enumTypeId;
    }

    /// <summary>
    ///     枚举类型记录
    /// </summary>
    private record EnumType
    {
        public int Id { get; init; }
        public required string FullName { get; init; }
        public required string AssemblyFullName { get; init; }
    }


    /// <summary>
    ///     路径枚举缓存记录
    /// </summary>
    private record PathEnumCache
    {
        public int Id { get; init; }
        public required string Path { get; init; }
        public required int EnumTypeId { get; init; }
    }
}